﻿CREATE procedure admin.s_help1
	@objname nvarchar(776) = NULL		-- object name we're after
as
	-- PRELIMINARY
	set nocount on
	declare	@dbname	sysname
		,@no varchar(35), @yes varchar(35), @none varchar(35)
	select @no = 'no', @yes = 'yes', @none = 'none'

	-- If no @objname given, give a little info about all objects.
	DECLARE @SQL nvarchar(max)
	if @objname is null
	begin
		-- DISPLAY ALL SYSOBJECTS --
		SET @SQL = '<TABLE>
		<HEAD><TR><TH>Name<TH>Owher<TH>Object_Type</HEAD>
		'
        select
            @SQL = @SQL + '<TR><TD>'          + o.name+
            '<TD>'         + user_name(ObjectProperty( object_id, 'ownerid'))+
            '<TD>'   + substring(v.name,5,31)
        from sys.all_objects o, master.dbo.spt_values v
        where o.type = substring(v.name,1,2) collate database_default and v.type = 'O9T'
        

		print @SQL

		-- DISPLAY ALL USER TYPES
		select
			'User_type'	= name,
			'Storage_type'	= type_name(system_type_id),
			'Length'		= max_length,
			'Prec'		= Convert(int,TypeProperty(user_type_id, 'precision')),
			'Scale'		= Convert(int,TypeProperty(user_type_id, 'scale')),
			'Nullable'		= case when is_nullable = 1 then @yes else @no end,
			'Default_name'	= isnull(object_name(default_object_id), @none),
			'Rule_name'		= isnull(object_name(rule_object_id), @none),
			'Collation'		= collation_name
		from sys.types
		where user_type_id > 256
		order by name

		return(0)
	end

	-- Make sure the @objname is local to the current database.
	select @dbname = parsename(@objname,3)
	if @dbname is null
		select @dbname = db_name()
	else if @dbname <> db_name()
		begin
			raiserror(15250,-1,-1)
			return(1)
		end

	-- @objname must be either sysobjects or systypes: first look in sysobjects
	declare @objid int
	declare @sysobj_type char(2)
	select @objid = object_id, @sysobj_type = type from sys.all_objects where object_id = object_id(@objname)

	-- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
	if @objid is null
	begin
		-- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME
		select @objid = type_id(@objname)

		-- IF NOT IN SYSTYPES, GIVE UP
		if @objid is null
		begin
			raiserror(15009,-1,-1,@objname,@dbname)
			return(1)
		end

		-- DATA TYPE HELP (prec/scale only valid for numerics)
		select
			'Type_name'	= name,
			'Storage_type'	= type_name(system_type_id),
			'Length'		= max_length,
			'Prec'			= Convert(int,TypeProperty(user_type_id, 'precision')),
			'Scale'			= Convert(int,TypeProperty(user_type_id, 'scale')),
			'Nullable'			= case when is_nullable=1 then @yes else @no end,
			'Default_name'	= isnull(object_name(default_object_id), @none),
			'Rule_name'		= isnull(object_name(rule_object_id), @none),
			'Collation'		= collation_name
		from sys.types
		where user_type_id = @objid

		return(0)
	end

	-- FOUND IT IN SYSOBJECT, SO GIVE OBJECT INFO
	select
		'Name'				= o.name,
		'Owner'				= user_name(ObjectProperty( object_id, 'ownerid')),
        'Type'              = substring(v.name,5,31),
		'Created_datetime'	= o.create_date
	from sys.all_objects o, master.dbo.spt_values v
	where o.object_id = @objid and o.type = substring(v.name,1,2) collate database_default and v.type = 'O9T'

	print ' '

	-- DISPLAY COLUMN IF TABLE / VIEW
	if exists (select * from sys.all_columns where object_id = @objid)
	begin

		-- SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE
		-- There must be a ',' immediately after each type name (including last one),
		-- because that's what we'll search for in charindex later.
		declare @precscaletypes nvarchar(150)
		select @precscaletypes = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,'

		-- INFO FOR EACH COLUMN
		select
			'Column_name'			= name,
			'Type'					= type_name(user_type_id),
			'Computed'				= case when ColumnProperty(object_id, name, 'IsComputed') = 0 then @no else @yes end,
			'Length'					= convert(int, max_length),
			-- for prec/scale, only show for those types that have valid precision/scale
			-- Search for type name + ',', because 'datetime' is actually a substring of 'datetime2' and 'datetimeoffset'
			'Prec'					= case when charindex(type_name(system_type_id) + ',', @precscaletypes) > 0
										then convert(char(5),ColumnProperty(object_id, name, 'precision'))
										else '     ' end,
			'Scale'					= case when charindex(type_name(system_type_id) + ',', @precscaletypes) > 0
										then convert(char(5),OdbcScale(system_type_id,scale))
										else '     ' end,
			'Nullable'				= case when is_nullable = 0 then @no else @yes end,
			'TrimTrailingBlanks'	= case ColumnProperty(object_id, name, 'UsesAnsiTrim')
										when 1 then @no
										when 0 then @yes
										else '(n/a)' end,
			'FixedLenNullInSource'	= case
						when type_name(system_type_id) not in ('varbinary','varchar','binary','char')
							then '(n/a)'
						when is_nullable = 0 then @no else @yes end,
			'Collation'		= collation_name
		from sys.all_columns where object_id = @objid

	end

	-- DISPLAY ANY PARAMS
	if exists (select * from sys.all_parameters where object_id = @objid)
	begin
		-- INFO ON PROC PARAMS
		select
			'Parameter_name'	= name,
			'Type'			= type_name(user_type_id),
			'Length'			= max_length,
			'Prec'			= case when type_name(system_type_id) = 'uniqueidentifier' then precision
								else OdbcPrec(system_type_id, max_length, precision) end,
			'Scale'			= OdbcScale(system_type_id, scale),
			'Param_order'		= parameter_id,
			'Collation'			= convert(sysname, case when system_type_id in (35, 99, 167, 175, 231, 239)
						then ServerProperty('collation') end)

		from sys.all_parameters where object_id = @objid
	end

	-- DISPLAY TABLE INDEXES & CONSTRAINTS
	if @sysobj_type in ('S ','U ')
	begin
		EXEC admin.s_helpindex @objname
		if exists(select * from sys.objects obj, sysdepends deps
				where obj.type ='V' and obj.object_id = deps.id and deps.depid = @objid
					and deps.deptype = 1)
		begin
            select distinct 'Table is referenced by views' = obj.name from sys.objects obj, sysdepends deps
				where obj.type ='V' and obj.object_id = deps.id and deps.depid = @objid
					and deps.deptype = 1 group by obj.name

		end
	end
	
	if @sysobj_type = 'U' and 
		 exists(SELECT * FROM sys.triggers AS tr INNER JOIN sys.tables AS t ON tr.parent_id = t.object_id WHERE t.object_id = @objid)
	begin
		print ' '

		SELECT tr.name AS [Trigger],dbo.clr_RegEx_MatchGroup(object_definition(tr.object_id),'FOR\s+(.+?)\s+AS\s',1) Type
		FROM         sys.triggers AS tr INNER JOIN
    sys.tables AS t ON tr.parent_id = t.object_id
    WHERE t.object_id = @objid
		
	end
	return (0) -- sp_help
